﻿using GWB.EntityGenerator.Entity;
using GWB.EntityGenerator.Method;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using WinFormDemo.Helper;

namespace GWB.EntityGenerator
{
    public partial class EntityGenerator : Form
    {
        private static bool Isok { get; set; } = false;
        //private static string ip = "";
        //private static string name = "";
        //private static string pwd = "";
        private static DatabaseEntity ThisAccount { get; set; }

        public EntityGenerator()
        {
            InitializeComponent();
            BindConnect();


        }

        /// <summary>
        /// 连接
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            Connect_Database();
        }

        /// <summary>
        ///连接并记住密码
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button3_Click(object sender, EventArgs e)
        {
            Connect_Database();
            if (Isok)
            {
                Writetxt(ThisAccount);
            }

        }


        /// <summary>
        /// 下拉框改变切换数据库
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {

            GetTables();
        }

        /// <summary>
        /// 绑定表实体
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {


            WriteClass();


        }

        /// <summary>
        /// 根据数据库查询表
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button2_Click(object sender, EventArgs e)
        {
            GetTables();

        }



        #region
        /// <summary>
        /// 连接数据库
        /// </summary>
        /// <returns></returns>
        private void Connect_Database()
        {
            timer1.Start();
            ThisAccount = new DatabaseEntity()
            {
                IP = textBox1.Text,
                Name = textBox2.Text,
                Pwd = textBox3.Text,
                Type = radioButton1.Checked ? "SqlService" : "MySql"
            };
            if (string.IsNullOrWhiteSpace(ThisAccount.Name) || string.IsNullOrWhiteSpace(ThisAccount.IP) || string.IsNullOrWhiteSpace(ThisAccount.Pwd))
            {
                MessageBoxEx.Show(this, "IP或账号错误", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button2);
                label5.Text = "连接失败";
                return;
            }
            try
            {
                var tableNameSql = ThisAccount.Type == "SqlService" ? "SELECT  convert(varchar(20),dbid)  as Id,Name FROM Master..SysDatabases ORDER BY Name"
                    : "SELECT TABLE_SCHEMA as name, TABLE_SCHEMA AS Id  from information_schema.tables  GROUP BY TABLE_SCHEMA";
                var tableNameList = GetTableInfo(ThisAccount.IP, ThisAccount.Name, ThisAccount.Pwd, ThisAccount.Type == "SqlService" ? "master" : "mysql", tableNameSql);
                if (Isok && tableNameList != null)
                {
                    comboBox1.SelectedIndexChanged -= comboBox1_SelectedIndexChanged;
                    comboBox1.DataSource = DataSetHelper.DataSetToEntityList<BaseTableEntity>(tableNameList, 0);
                    comboBox1.ValueMember = "Id";
                    comboBox1.SelectedIndexChanged += new EventHandler(comboBox1_SelectedIndexChanged);
                    comboBox1.DisplayMember = "Name";
                }

            }
            catch (Exception ex)
            {
                MessageBoxEx.Show(this, "查询异常！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button2);
            }
        }


        /// <summary>
        /// 连接数据库
        /// </summary>
        /// <param name="ip"></param>
        /// <param name="name"></param>
        /// <param name="pwd"></param>
        /// <param name="dataBase"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        private DataSet GetTableInfo(string ip, string name, string pwd, string dataBase, string sql)
        {
            string connsql = "server=" + ip + $";database={dataBase};uid=" + name + ";pwd=" + pwd + ";";

            //server = 106.12.108.233; database = mysql; uid = sa; pwd = 158922123a.; Timeout = 5

           // connsql = @"Server = 106.12.108.233; User ID = root; Password = 158922123a.; ";
            pictureBox1.Show();//显示
            try
            {
               
                DataSet dt = new DataSet(); // 实例化数据表
                if (ThisAccount.Type == "SqlService")
                {
                    using (SqlConnection conn = new SqlConnection())
                    {
                        conn.ConnectionString = connsql;
                        conn.Open(); // 打开数据库连接
                        SqlDataAdapter myda = new SqlDataAdapter(sql, conn); // 实例化适配器
                        myda.Fill(dt); // 保存数据 
                        conn.Close(); // 关闭数据库连接
                    }
                }
                else
                {
                    using (MySqlConnection conn = new MySqlConnection())
                    {
                        conn.ConnectionString = connsql;
                        conn.Open(); // 打开数据库连接
                        MySqlDataAdapter myda = new MySqlDataAdapter(sql, conn); // 实例化适配器
                        myda.Fill(dt); // 保存数据 
                        conn.Close(); // 关闭数据库连接
                    }
                }

                label5.Text = "连接成功";
                Isok = true;
                pictureBox1.Hide();//关闭
                return dt;
            }
            catch (Exception ex)
            { 
                pictureBox1.Hide();//关闭

                if (ex.Message.Contains($"Cannot open database \"{dataBase}\" requested by the login"))
                {
                    MessageBoxEx.Show(this, $"用户：{name} 访问 { dataBase } 失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button2);
                }
                else
                {
                    Isok = false;
                    label5.Text = "连接失败";
                    MessageBoxEx.Show(this, "数据库连接错误！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button2);

                }
                return null;
            }

        }

        /// <summary>
        /// 获取表集合
        /// </summary>
        private void GetTables()
        {
            try
            {
                if (Isok)
                {
                    if (!string.IsNullOrWhiteSpace(comboBox1.Text))
                    {
                        var where = "";
                        if (!string.IsNullOrWhiteSpace(textBox4.Text))
                        {
                            where = $" where  TABLE_NAME like '%{textBox4.Text}%'";
                        }
                        var tableNameSql = ThisAccount.Type == "SqlService" ? $" SELECT TABLE_NAME AS Id,TABLE_NAME AS Name FROM INFORMATION_SCHEMA.TABLES {where} order by TABLE_NAME"
                            : $" SELECT table_name as name, TABLE_NAME as id from  information_schema.tables WHERE TABLE_SCHEMA='{comboBox1.Text}{where}' order by TABLE_NAME";

                        var tableNameList = GetTableInfo(ThisAccount.IP, ThisAccount.Name, ThisAccount.Pwd, comboBox1.Text, tableNameSql);
                        listBox1.SelectedIndexChanged -= new EventHandler(listBox1_SelectedIndexChanged);
                        listBox1.DataSource = DataSetHelper.DataSetToEntityList<BaseTableEntity>(tableNameList, 0);
                        listBox1.ValueMember = "Id";
                        listBox1.SelectedIndexChanged += new EventHandler(listBox1_SelectedIndexChanged);
                        listBox1.DisplayMember = "Name";
                    }
                }

            }
            catch (Exception ex)
            {
                MessageBoxEx.Show(this, "请选者正确的表名！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button2);
            }
        }

        /// <summary>
        /// 查询表的实体
        /// </summary>
        private void WriteClass()
        {

            var sql = ThisAccount.Type == "SqlService" ? $@"SELECT  
                           Name = a.name, 
                          Type = b.name, 
                          Explain = isnull(g.[value], '')
                          FROM syscolumns a
                          left join systypes b on a.xtype = b.xusertype
                          inner join sysobjects d on a.id = d.id and d.xtype = 'U' and d.name <> 'dtproperties'
                          left join syscomments e on a.cdefault = e.id
                          left join sys.extended_properties g on a.id = g.major_id and a.colid = g.minor_id
                          where d.name = '{listBox1.Text}'--如果只查询指定表,加上此条件
                         order by a.id,a.colorder "
                : $@"select column_name as name,
                            data_type as Type,
	                        column_comment as 'Explain'
                            from information_schema.columns where table_name='{listBox1.Text}' ";

            var tableNameList = GetTableInfo(ThisAccount.IP, ThisAccount.Name, ThisAccount.Pwd, comboBox1.Text, sql);
            if (!Isok) return;
            var list = DataSetHelper.DataSetToEntityList<BaseTableEntity>(tableNameList, 0);
            richTextBox1.Multiline = true;
            richTextBox1.Text = BaseMethod.ClassArrange("", listBox1.Text, list == null ? new List<BaseTableEntity>() : list.ToList());
            BaseMethod.SetFont(richTextBox1, "", listBox1.Text, list == null ? new List<BaseTableEntity>() : list.ToList());

        }


        /// <summary>
        /// 获取保存的数据库连接
        /// </summary>
        /// <returns></returns>
        public List<DatabaseEntity> Redtxt()
        {
            string path = Application.StartupPath;
            label6.Text = path;

            try
            {
                var result = new List<DatabaseEntity>();
                Directory.Exists(Application.StartupPath.Substring(0, path.LastIndexOf("\\")) + "//DatabaseConnection.txt");
                // 创建一个 StreamReader 的实例来读取文件 
                // using 语句也能关闭 StreamReader
                using (StreamReader sr = new StreamReader(Application.StartupPath.Substring(0, path.LastIndexOf("\\")) + "//DatabaseConnection.txt", Encoding.UTF8))
                {
                    string line;

                    // 从文件读取并显示行，直到文件的末尾 
                    while ((line = sr.ReadLine()) != null)
                    {
                        var strs = line.Split();
                        if (strs.Count() >= 4)
                        {
                            result.Add(new DatabaseEntity()
                            {
                                IP = strs[0].Trim(),
                                Name = strs[1].Trim(),
                                Pwd = strs[2].Trim(),
                                Type = strs[3].Trim(),
                            }); ;
                        }
                    }
                }
                return result;
            }
            catch (Exception)
            {
                return new List<DatabaseEntity>();

            }
        }

        /// <summary>
        /// 新增数据库连接
        /// </summary>
        /// <param name="entity"></param>
        public void Writetxt(DatabaseEntity entity)
        {
            string path = Application.StartupPath;
            try
            {
                var list = Redtxt();
                if (list.Select(x => x.Name == entity.Name && x.IP == entity.IP && x.Pwd == entity.Pwd).Contains(true))
                {
                    return;
                }
                list.Add(entity);
                var str = "";
                foreach (var item in list)
                {
                    str += $"{item.IP} {item.Name} {item.Pwd} {item.Type}\n";
                }

                using (StreamWriter sw = new StreamWriter(Application.StartupPath.Substring(0, path.LastIndexOf("\\")) + "//DatabaseConnection.txt"))
                {
                    sw.WriteLine(str);
                }
            }
            catch (Exception ex)
            {
                MessageBoxEx.Show(this, "写入错误！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button2);

            }


        }

        /// <summary>
        /// 绑定连接
        /// </summary>
        public void BindConnect()
        {
            pictureBox1.Hide();//关闭
            radioButton1.Checked = true;
            var list = Redtxt();
            textBox1.DataSource = list;
            textBox1.ValueMember = "IP";
            textBox1.DisplayMember = "IP";

            textBox2.DataSource = list;
            textBox2.ValueMember = "Name";
            textBox2.DisplayMember = "Name";

            textBox3.DataSource = list;
            textBox3.ValueMember = "Pwd";
            textBox3.DisplayMember = "Pwd";

            foreach (var item in list)
            {
                if (textBox1.Text != item.IP || textBox2.Text != item.Name || textBox3.Text != item.Pwd) continue;
                if (item.Type == "SqlService")
                {
                    radioButton1.Checked = true;
                }
                else
                {
                    radioButton2.Checked = true;
                }
            }


        }


        public void Loading(bool isLoading)
        {

            if (isLoading)
            {
                timer1.Start();
            }
            else
            {
                timer1.Stop();
            }


        }


        #endregion

        private void timer1_Tick(object sender, EventArgs e)
        {
            //var count = label6.Text.Count(x => x == '>');

            //var str = "";

            //for (int i = 0; i <= 154; i++)
            //{
            //    if (count>154)
            //    {
            //        str = "";
            //    }
            //    str = str + ">";
            //}
            //label6.Text = label6.Text.Replace(">", "")+ str;
        }

        private void groupBox3_Enter(object sender, EventArgs e)
        {
            label6.Text = Application.StartupPath;
        }

        private void textBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            var list = Redtxt();
            foreach (var item in list)
            {
                if (textBox1.Text != item.IP || textBox2.Text != item.Name || textBox3.Text != item.Pwd) continue;
                if (item.Type == "SqlService")
                {
                    radioButton1.Checked = true;
                }
                else
                {
                    radioButton2.Checked = true;
                }
            }
        }

        private void textBox2_SelectedIndexChanged(object sender, EventArgs e)
        {
            var list = Redtxt();
            foreach (var item in list)
            {
                if (textBox1.Text != item.IP || textBox2.Text != item.Name || textBox3.Text != item.Pwd) continue;
                if (item.Type == "SqlService")
                {
                    radioButton1.Checked = true;
                }
                else
                {
                    radioButton2.Checked = true;
                }
            }
        }

        private void textBox3_SelectedIndexChanged(object sender, EventArgs e)
        {
            var list = Redtxt();
            foreach (var item in list)
            {
                if (textBox1.Text != item.IP || textBox2.Text != item.Name || textBox3.Text != item.Pwd) continue;
                if (item.Type == "SqlService")
                {
                    radioButton1.Checked = true;
                }
                else
                {
                    radioButton2.Checked = true;
                }
            }
        }

        private void customInstaller1_AfterInstall(object sender, System.Configuration.Install.InstallEventArgs e)
        {

        }
    }
}
